﻿// Sử dụng class này để kết nối đến SQL trên máy người dùng hoặc SQL máy khác trong mạng Lan



using System.Data;
using System.ServiceModel;
using System.Collections.Generic;
using System;
using Lib.CommonForm;
using System.Data.SqlClient;
using System.Data.Common;

public class DataConnect
{
    /*
    #region "Transaction"

    private static Dictionary<int, DbTransaction> m_lstTransaction = new Dictionary<int, DbTransaction>();
    private static Random m_rndTransaction = new Random(1);
    private static Dictionary<string, int> m_lstTransID = new Dictionary<string, int>();
    private static Dictionary<int, string> m_dicThreadTrans = new Dictionary<int, string>();

    private static DbTransaction Transaction_GetById(int transactionID)
    {
        if (m_lstTransaction.ContainsKey(transactionID))
        {
            return m_lstTransaction[transactionID];
        }
        else
        {
            throw new Exception("Invalid transaction ID");
        }
    }

    private static SqlTransaction Transaction_GetSql(int transactionID)
    {
        if (m_lstTransaction.ContainsKey(transactionID))
        {
            return (SqlTransaction)m_lstTransaction[transactionID];
        }
        else
        {
            throw new Exception("Invalid transaction ID");
        }
    }

    //private static OracleTransaction Transaction_GetOra(int transactionID)
    //{
    //    if (m_lstTransaction.ContainsKey(transactionID))
    //    {
    //        return (OracleTransaction)m_lstTransaction[transactionID];
    //    }
    //    else
    //    {
    //        throw new Exception("Invalid transaction ID");
    //    }
    //}

    private static bool Transaction_IsSql(int transactionID)
    {
        return (m_lstTransaction[transactionID] is SqlTransaction);
    }


    public static bool Check_Trans(ref string sTransName)
    {
        try
        {
            if (string.IsNullOrEmpty(sTransName) && m_dicThreadTrans.ContainsKey(System.Threading.Thread.CurrentThread.ManagedThreadId))
            {
                sTransName = m_dicThreadTrans[System.Threading.Thread.CurrentThread.ManagedThreadId];
            }
            return m_lstTransID.ContainsKey(sTransName);
        }
        catch
        {
            return false;
        }
    }

    
    public static string Begin_Trans()
    {
        string sTransName = "";
        //Check current thread transaction
        if (Check_Trans(ref sTransName))
        {
            return sTransName;
        }
        sTransName = Guid.NewGuid().ToString();
        int transID = 0;
        if (IsRemote)
        {
            transID = m_DataRemoteHelper.Begin_Trans();
        }
        else
        {
            transID = m_rndTransaction.Next();
            while (m_lstTransaction.ContainsKey(transID))
            {
                transID = m_rndTransaction.Next();
            }
            DbTransaction trans = default(DbTransaction);
            if (Globals.ConnectString_IsSql(WorkingConnect))
            {
                SqlConnection conn = new SqlConnection(WorkingConnect);
                conn.Open();
                trans = conn.BeginTransaction();
            }
            else
            {
                //OracleConnection conn = new OracleConnection(WorkingConnect);
                //conn.Open();
                //trans = conn.BeginTransaction();
            }
            m_lstTransaction.Add(transID, trans);
        }
        //
        m_lstTransID.Add(sTransName, transID);
        //Store in current thread
        if (m_dicThreadTrans.ContainsKey(System.Threading.Thread.CurrentThread.ManagedThreadId))
        {
            m_dicThreadTrans[System.Threading.Thread.CurrentThread.ManagedThreadId] = sTransName;
        }
        else
        {
            m_dicThreadTrans.Add(System.Threading.Thread.CurrentThread.ManagedThreadId, sTransName);
        }
        //
        return sTransName;
    }

    public static bool Rollback_Trans()
    {
        bool retVal = false;
        string sTransName = "";
        //Check current thread transaction
        if (Check_Trans(ref sTransName))
        {
            retVal = Rollback_Trans(sTransName);
            m_dicThreadTrans[System.Threading.Thread.CurrentThread.ManagedThreadId] = "";
        }
        return retVal;
    }

    public static bool Rollback_Trans(string sTransName)
    {
        try
        {
            int _TransactionID = m_lstTransID[sTransName];
            if (IsRemote)
            {
                m_DataRemoteHelper.Rollback_Trans(_TransactionID);
            }
            else
            {
                DbTransaction trans = Transaction_GetById(_TransactionID);
                trans.Rollback();
                if ((trans.Connection != null))
                    trans.Connection.Close();
                trans.Dispose();
                m_lstTransaction.Remove(_TransactionID);
            }
            m_lstTransID.Remove(sTransName);
            return true;
        }
        catch
        {
            return false;
        }
    }

    public static bool Commit_Trans()
    {
        bool retVal = false;
        string sTransName = "";
        //Check current thread transaction
        if (Check_Trans(ref sTransName))
        {
            retVal = Commit_Trans(sTransName);
            m_dicThreadTrans[System.Threading.Thread.CurrentThread.ManagedThreadId] = "";
        }
        return retVal;
    }

    public static bool Commit_Trans(string sTransName)
    {
        try
        {
            int _TransactionID = m_lstTransID[sTransName];
            if (IsRemote)
            {
                m_DataRemoteHelper.Commit_Trans(_TransactionID);
            }
            else
            {
                DbTransaction trans = Transaction_GetById(_TransactionID);
                trans.Commit();
                if ((trans.Connection != null))
                    trans.Connection.Close();
                trans.Dispose();
                m_lstTransaction.Remove(_TransactionID);
            }
            //
            m_lstTransID.Remove(sTransName);
            return true;
        }
        catch
        {
            return false;
        }
    }
    #endregion
    */
    public static Lib.CommonForm.ServiceWCF.ServiceClient MainServiceWCF()
    {
        ConnectLog rs = new ConnectLog();
        string uri = rs.ServiceAddress;
        Lib.CommonForm.ServiceWCF.ServiceClient wcf = new Lib.CommonForm.ServiceWCF.ServiceClient(ProxyConfig.GetBinding(uri, true), new EndpointAddress(uri));
        wcf.ClientCredentials.UserName.UserName = FormGlobals.strUserName;
        wcf.ClientCredentials.UserName.Password = FormGlobals.strPassword;
        wcf.Open();
        return wcf;
    }

    public static Lib.CommonForm.ServiceWCF.MTomClient MtomServiceWCF()
    {
        ConnectLog rs = new ConnectLog();
        string uri = rs.ServiceAddress;
        Lib.CommonForm.ServiceWCF.MTomClient wcf = new Lib.CommonForm.ServiceWCF.MTomClient(ProxyConfig.GetBinding(uri, false), new EndpointAddress(uri + "/MTom"));
        wcf.Open();
        return wcf;
    }


    public static int ExecuteNonQuery(string spName, params object[] parameterValues)
    {
        int retVal = 0;
        Lib.CommonForm.ServiceWCF.ServiceClient svc = MainServiceWCF();
        try
        {
            retVal = svc.ExecuteNonQuery(spName, DicAndParam.ToDictionary(parameterValues));
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            svc.Close();
        }
        return retVal;
    }

    public static int ExecuteNonQuery(string sql)
    {

        return ExecuteNonQuery(sql, null);
    }

    public static DataSet ExecuteDataset(string spName, params object[] parameterValues)
    {
        DataSet retVal = null;
        Lib.CommonForm.ServiceWCF.ServiceClient svc = MainServiceWCF();
        try
        {
            retVal = svc.ExecuteDataset(spName, DicAndParam.ToDictionary(parameterValues));
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            svc.Close();
        }
        return retVal;
    }

    public static DataSet ExecuteDataset(string sql)
    {

        return ExecuteDataset(sql, null);
    }

    public static DataTable ExecuteDatatable(string spName, params object[] parameterValues)
    {
        DataTable retVal = null;
        DataSet ds = ExecuteDataset(spName, parameterValues);
        if (ds != null)
        {
            if (ds.Tables.Count > 0)
                retVal = ds.Tables[0];
        }
        return retVal;
    }

    public static DataTable ExecuteDatatable(string sql)
    {
        return ExecuteDatatable(sql, null);
    }

    public static object ExecuteScalar(string spName, params object[] parameterValues)
    {
        object retVal = null;
        Lib.CommonForm.ServiceWCF.ServiceClient svc = MainServiceWCF();
        try
        {
            retVal = svc.ExecuteScalar(spName, DicAndParam.ToDictionary(parameterValues));
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            svc.Close();
        }
        return retVal;
    }

    public static object ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, null);
    }


}

